const pool = require('../database/conn')

const getMsgById = async (chat_id, msgOffset) => {
    const sql = 'SELECT * FROM\n' +
        '(SELECT\n' +
        '    messages.id,\n' +
        '    messages.sender_id, \n' +
        '    messages.text,\n' +
        '    messages.audio,\n' +
        '    messages.send_time,\n' +
        '    messages.edited_at,\n' +
        '    users.name,\n' +
        '    users.avatar,\n' +
        '    GROUP_CONCAT(`attachments`.mime_type) AS mimeType,\n'+
        '    GROUP_CONCAT(`attachments`.filename) AS filenames\n' +
        'FROM messages\n' +
        'JOIN users ON messages.sender_id = users.id\n' +
        'LEFT JOIN attachments ON attachments.message_id = messages.id\n' +
        'WHERE messages.chat_id = ? \n' +
        'GROUP BY messages.id\n' +
        'ORDER BY send_time DESC\n' +
        'LIMIT ?, 25) AS subq ORDER BY send_time ASC'
    const [rows] = await pool.query(sql, [chat_id, msgOffset])
    return rows
}

const getVoiceById = async (room_number, msg_id) => {
    const sql = `SELECT voice FROM message_group${room_number} where id = ?`

    const [rows] = await pool.query(sql, [msg_id])
    return rows[0].voice
}

const createMsg = async (chat_id, sender_id, text, send_time) =>{
    const sql = 'INSERT INTO `messages` (chat_id, sender_id, text, send_time) VALUES (?, ?, ?, ?); SELECT LAST_INSERT_ID() as last_id'
    const [rows] = await pool.query(sql, [chat_id, sender_id, text, send_time])
    return rows[1]
}

const createAttachments = async (message_id, url, filename, size, mimetype) =>{
    const sql = 'INSERT INTO `attachments` (message_id, url, filename, size, mime_type) VALUES (?, ?, ?, ?, ?);'
    const [rows] = await pool.query(sql, [message_id, url, filename, size, mimetype])
    return rows
}

const getAttachmentsByMsgId = async (message_id) => {
    const sql = 'SELECT GROUP_CONCAT(`attachments`.filename) as filenames, GROUP_CONCAT(`attachments`.mime_type) as mimeType FROM `attachments` WHERE message_id = ?'
    const [row] = await pool.query(sql, [message_id])
    return row
}

const updateMsgByMsgId = async (content, msgId) => {
    const sql = 'UPDATE `messages` SET `text` = ?, edited_at = NOW() WHERE ID = ?'
    const [rs] = await pool.query(sql, [content, msgId])
    return rs
}

const deleteMsgById = async (msgId) => {
    const sql = 'DELETE FROM `messages` WHERE `messages`.id = ?;' +
        'DELETE FROM `attachments` WHERE `message_id` = ?'

    const rs = pool.query(sql, [msgId, msgId])
    return rs
}

const getAttachmentsUrlByMsgId = async (msgId) =>{
    const sql = 'SELECT `attachments`.url FROM `attachments` WHERE message_id = ?'
    const [row] = await pool.query(sql, [msgId])
    return row
}

const getDmOppUserByConId = async (conId, uid) => {
    const sql = 'SELECT\n' +
        '    users.name, \n' +
        '    users.avatar, \n' +
        '    users.id,\n' +
        '    users.create_time,\n' +
        '    user_profile.* \n' +
        'FROM\n' +
        '    users\n' +
        '    JOIN dm_members ON users.id = dm_members.user_id \n' +
        '    LEFT JOIN user_profile ON users.id = user_profile.uid\n' +
        'WHERE\n' +
        '    dm_members.conversation_id = ? \n' +
        '    AND users.id != ?'
    const [row] = await pool.query(sql, [conId, uid])
    return row
}

module.exports = {
    getMsgById,
    getVoiceById,
    createMsg,
    createAttachments,
    getAttachmentsByMsgId,
    getAttachmentsUrlByMsgId,
    updateMsgByMsgId,
    deleteMsgById,
    getDmOppUserByConId
}